package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.config.DBConfig;
import com.qf.studentmanager.model.Course;
import com.qf.studentmanager.model.Grade;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDao {
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
    //根据年级名称查是否有已存在。
    public Grade getGradeByGradeName(String gradeName) {
        String sql = "select * from grade where gradeName=?";
        try {
            Grade grade = queryRunner.query(sql, new BeanHandler<>(Grade.class), gradeName);
            return  grade;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public Integer addGrade(Grade grade) throws SQLException {
        Connection connection = DBConfig.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("insert into grade  set gradeName = ?;", Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, grade.getGradeName());//只有一个参数
        int i = preparedStatement.executeUpdate();//执行了这个运行语句后，会有一个主键含在preparedStatement中，用get方法获取。
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()){
            int anInt = resultSet.getInt(1);//拿第一个主键，其实只有一个。
            grade.setGid(anInt); //把主键填入上面传入这个方法的参数grade里面。
        }

       return i;
    }


    public Integer addGradeAndCourse(Integer gid, Integer[] cs) throws SQLException {
        Object[] params = new Object[cs.length*2];
        StringBuffer sql = new StringBuffer("insert into grade_course(gid,cid) values");
        for (int i = 0; i < cs.length; i++) {
            if (i == cs.length-1){
                sql.append("(?,?)");
            }else {
                sql.append("(?,?),");
            }
            params[i*2] = gid;
            params[i*2+1] = cs[i];
        }
        Connection connection = DBConfig.getConnection();
        int i = queryRunner.update(connection, sql.toString(), params);
        return i;


    }

    public List<Grade> getAllGrade() {
        try {
            String sql ="select  g.*,c.* from grade g left join grade_course gc on g.gid = gc.gid left join  course c on gc.cid = c.cid";
            return queryRunner.query(sql, new ResultSetHandler<List<Grade>>() {
                @Override
                public List<Grade> handle(ResultSet resultSet) throws SQLException {
                    List<Grade> grades = new ArrayList<>();
                    int lastGid = -1;
                    Grade g = null;
                    while (resultSet.next()) {
                        int gid = resultSet.getInt("gid");
                        if (lastGid == gid) {
                            int cid = resultSet.getInt("cid");
                            String courseName = resultSet.getString("courseName");
                            Course course = new Course();
                            course.setCid(cid);
                            course.setCourseName(courseName);
                            g.getCourses().add(course);//不能像下面新建一个list来放，因为里面那个list《course》已经有东西了，只能把这个list拿出来往里面放下一个course
                        } else {
                            String gradeName = resultSet.getString("gradeName");
                            int cid = resultSet.getInt("cid");
                            String courseName = resultSet.getString("courseName");
                            g = new Grade();
                            g.setGid(gid);
                            g.setGradeName(gradeName);
                            Course course = new Course();
                            course.setCid(cid);
                            course.setCourseName(courseName);
                            List<Course> courses = new ArrayList<>();
                            courses.add(course);
                            g.setCourses(courses);
                            grades.add(g);
                        }
                        lastGid = gid;
                    }
                    return grades;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }

    public Integer deleteGradeByGid(int gid) throws SQLException {
        Connection connection = DBConfig.getConnection();
        return queryRunner.update(connection, "delete from grade where gid= ?;",gid);
    }

    public Integer deleteGradeAndCourse(int gid) throws SQLException {
        return queryRunner.update(DBConfig.getConnection(), "delete from grade_course where gid= ?;",gid);
    }
}
